package com.googlecode.bip.dao.mysql;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.NamingException;
import javax.servlet.http.HttpServletRequest;
import org.apache.log4j.Logger;
import com.googlecode.bip.dao.UserDAO;
import com.googlecode.bip.dao.utils.DAOBuilder;
import com.googlecode.bip.dao.utils.ListableInstance;
import com.googlecode.bip.entity.IGenericUser;
import com.googlecode.bip.entity.IUser;
import com.googlecode.bip.entity.IllegalIdLocalException;
import com.googlecode.bip.entity.Role;
import com.googlecode.bip.entity.impl.BipUser;
import com.googlecode.bip.util.RolesIdentifier;
import cx.ath.choisnet.io.InputStreamHelper;
import cx.ath.choisnet.sql.ConnectionQuery;
import cx.ath.choisnet.sql.SQLTools;

/**
 *
 * @author Claude CHOISNET
 */
public class MySQLUserDAO
    extends MySQLDAOBuilder<IUser>
        implements UserDAO, ListableInstance<IUser>
{
    private static final long serialVersionUID = 2L;
    private static final Logger logger = Logger.getLogger( MySQLUserDAO.class );
    private final String defaultCharsetName;

    public MySQLUserDAO( final String defaultCharsetName )
    {
        this.defaultCharsetName = defaultCharsetName;
    }

    @Override //ListableInstance<IUser>
    public IUser newInstance( final ResultSet rSet )
          throws SQLException
    {
        return new BipUser().set( rSet );
    }

    @Override
    public IUser findUser( final int iduser ) throws NamingException, SQLException
    {
        final String sql = "SELECT * FROM `usersview` WHERE `iduser`='"
            + iduser
            + "'";

        return getElement( sql );
    }

    @Override
    public IUser findUser( final HttpServletRequest request )
        throws NamingException, SQLException
    {
        final String username = request.getRemoteUser();

        if( username != null ) {
            final String sql = "SELECT * FROM `usersview` WHERE `username`='"
                + SQLTools.parseFieldValue( username )
                + "'";

            return getElement( sql );
            }

        return null;
    }

    @Override
    public IGenericUser findGenericUser(
        final String username,
        final String password
        )
        throws NamingException, SQLException
    {
        final MySQLIGenericUserDAO dao = new MySQLIGenericUserDAO();
        final String sql = "SELECT * FROM `allusers` WHERE `username`='"
            + SQLTools.parseFieldValue( username )
            + "' AND `password`='"
            + SQLTools.parseFieldValue( password )
            + "';";

        return dao.getElement( sql );
    }

    @Override
    public IGenericUser findGenericUser( final HttpServletRequest request )
            throws NamingException, SQLException
    {
        final String username = request.getRemoteUser();

        if( username != null ) {
            MySQLIGenericUserDAO dao = new MySQLIGenericUserDAO();

            final String sql = "SELECT * FROM `allusers` WHERE `username`='"
                + SQLTools.parseFieldValue( username )
                + "'";

            return dao.getElement( sql );
            }

        return null;
    }

    @Override
    public IGenericUser findGenericUser( final int iduser ) throws NamingException,
            SQLException
    {
        MySQLIGenericUserDAO dao = new MySQLIGenericUserDAO();

        final String sql = "SELECT * FROM `allusers` WHERE `iduser`='"
            + iduser
            + "'";

        return dao.getElement( sql );
    }

    /**
     * Insert a new User
     * @param user User to insert
     * @return true if User has been created
     */
    @Override
    public IUser insertUser( final String username, final String password )
        throws NamingException, SQLException
    {
        final String sqlUserName = SQLTools.parseFieldValue( username );
        final String sqlPassword = SQLTools.parseFieldValue( password );

        try {
            final String sql = "CALL add_bip_user('"
                + sqlUserName
                + "','"
                + sqlPassword
                + "')"
                ;

            int iduser = private_connectUser( sql, sqlUserName );

            return findUser( iduser );
            }
        catch( Exception e ) {
            logger.error( "*** createUser: ", e );

            return null;
            }
    }

    @Override
    public boolean updateUser( final IUser iuser )
        throws NamingException, SQLException, IllegalIdLocalException
    {
        BipUser user = BipUser.class.cast( iuser );

        final StringBuilder sql = new StringBuilder();

        sql.append( "UPDATE `users` AS U SET U.`idlocal`='" );

        sql.append( MySQLLocalDAO.static_getValidIdLocal( user.getIdLocal() ) )
           .append( "', U.`timezone`=" );

        String formattedTimeZone = user.formatTimeZone();

        if( formattedTimeZone == null ) {
            sql.append( "NULL" );
            }
        else {
            sql.append( "'" )
               .append( formattedTimeZone )
               .append( "'" );
            }

        sql.append( ", U.`usermail`=" );

        if( iuser.getEMail() == null ) {
            sql.append( "NULL" );
            }
        else {
            sql.append( "'" )
               .append( SQLTools.parseFieldValue( iuser.getEMail() ) )
               .append( "'" );
            }

        sql.append( ", U.`userdescription`=" );

        if( iuser.getUserDescription() == null ) {
            sql.append( "NULL" );
            }
        else {
            sql.append( "'" )
               .append( SQLTools.parseFieldValue( iuser.getUserDescription() ) )
               .append( "'" );
            }

        sql.append( " WHERE U.`iduser`='" )
           .append( user.getIdUser() )
           .append( "';" );

        return doUpdate( sql.toString() ) == 1;
    }

    // ------------------------------------------------------------
    // -------- IGenericUser
    // ------------------------------------------------------------

    @Override
    public IGenericUser insertGenericUser(
            final String    username,
            final String    password,
            final Role      role
            )
        throws NamingException, SQLException
    {
        if( Role.BIP_USER.equals( role ) ) {
            return insertUser( username, password );
            }
        if( Role.BIP_REF.equals( role ) ) {
            return new MySQLReferentDAO().insertReferent( username, "", username, password );
            }

        // TODO insertGenericUser NOT IMPLEMENTED

        return null;
    }

    @Override
    public boolean updateGenericUser( final IGenericUser iGenericUser )
            throws NamingException, SQLException
    {
        final String sql = "UPDATE `allusers` AS U SET U.`username`='"
                + SQLTools.parseFieldValue( iGenericUser.getUserName() )
                + "', U.`isactive`='"
                + iGenericUser.getIsActive()
                + "' WHERE U.`iduser`="
                + iGenericUser.getIdUser()
                + ";";

        doUpdate( sql );

        return true;
    }

    @Override
    public boolean changeGenericUserPassword(
            final int    iduser,
            final String oldPassword,
            final String newPassword
            )
            throws NamingException, SQLException
    {
        final String sql = "UPDATE `allusers` AS U SET U.`password`='"
                + SQLTools.parseFieldValue( newPassword )
                + "' WHERE U.`iduser`="
                + iduser
                + " AND U.`password`='"
                + SQLTools.parseFieldValue( oldPassword )
                + "';";

        int changeCount = doUpdate( sql );

        if( logger.isDebugEnabled() ) {
            logger.debug( "changeGenericUserPassword : " + changeCount );
            }

        return true;
    }

    /**
     * Delete a User
     * @param user User to delete
     * @return true if User has been deleted, false on failure
     * @throws NamingException
     * @throws SQLException
     */
    @Override
    public boolean deleteUser( final int iduser )
        throws NamingException, SQLException
    {
        final String sql = "CALL delete_bip_user( '" + iduser + "' );";

        doUpdate( sql ); // == 2 ?

        return true;
    }


    /**
     *
     * @param sql
     * @param sqlusername username (must be SQL safe)
     * @return
     * @throws SQLException
     * @throws NamingException
     */
    private int private_connectUser(
            final String    sql,
            final String    sqlusername
            ) throws SQLException, NamingException
    {
        logger.info( "connectUser: " + sql );

        Connection connection = getConnection();
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            ResultSet   rSet        = query.executeQuery( sql );
            int         count       = 0;
            int         iduser      = 0;
            String      uname       = null;

            while( rSet.next() ) {
                iduser  = rSet.getInt( "iduser" );
                uname   = rSet.getString( "username" );
                count++;
                }

            if( count == 1 ) {
                // Extra check (security)
                if( sqlusername.equals( uname ) && ! sqlusername.isEmpty() ) {

                    return iduser;
                    }
                }

            logger.warn(
                "*** connectUser: result count for '" + sqlusername
                    + "' : " + count
                    + " - uname: " + uname
                    );
            }
        catch( SQLException e ) {
            logger.error( "*** connectUser: " + sql, e );
            }
        finally {
            query.quietClose();
            closeConnection( connection );
            }

        // An error is occurred !
        return 0;
    }

    @Override
    public String getGenericUserConfig(
             final IGenericUser iGenericUser
             ) throws SQLException, NamingException, IOException
    {
        byte[] b = getGenericUserConfigByteArray( iGenericUser );

        if( b == null ) {
            return null;
            }
        else {
            return new String( b, this.defaultCharsetName );
            }
    }

    private byte[] getGenericUserConfigByteArray(
             final IGenericUser iGenericUser
             )
        throws SQLException, NamingException, IOException
    {
        final String                sql = "SELECT `json` FROM `allusers` WHERE `iduser`=?";
        final PreparedStatement     ps  = getConnection().prepareStatement( sql );
        final ByteArrayOutputStream os  = new ByteArrayOutputStream();

        try {
             ps.setInt(1, iGenericUser.getIdUser());
             ResultSet rs = ps.executeQuery();

             try {
                 if( rs.next() ) {
                     InputStream is = rs.getBinaryStream( "json" );

                     if( is != null ) {
                         InputStreamHelper.copy( is, os );
                         }
                     else {
                         return null;
                         }
                     }
                 }
             finally {
                 rs.close();
                 }
             }
        finally {
            ps.close();
            }

         return os.toByteArray();
     }

    private boolean updateGenericUserConfig(
            final IGenericUser  iGenericUser,
            final InputStream   is,
            final int           length
            ) throws SQLException, NamingException
    {
        final String            sql = "UPDATE `allusers` AS U SET U.`json`=? WHERE U.`iduser`=? ;";
        final PreparedStatement ps  = getConnection().prepareStatement( sql );

       try {
           ps.setBinaryStream(1, is, length );
           ps.setInt(2, iGenericUser.getIdUser());

           ps.executeUpdate();
           }
       finally {
           ps.close();
           }

        return true;
    }

    private boolean updateGenericUserConfig(
            final IGenericUser  iGenericUser,
            final byte[]        config
            ) throws SQLException, NamingException
    {
        if( config == null ) {
            return updateGenericUserConfig( iGenericUser, (InputStream)null, 0 );
            }
        else {
            final int           length  = config.length;
            final InputStream   is      = new ByteArrayInputStream( config );

           try {
               return updateGenericUserConfig( iGenericUser, is, length );
               }
           finally {
               try { is.close();} catch( IOException ignore ) {}
               }
            }
    }

    private boolean updateGenericUserConfig(
            final IGenericUser  iGenericUser,
            final String        config,
            final String        charset
            )
            throws NamingException, SQLException, UnsupportedEncodingException
    {
        if( config == null ) {
            return updateGenericUserConfig( iGenericUser, (byte[])null );
            }
        else {
            return updateGenericUserConfig( iGenericUser, config.getBytes( charset ) );
        }
    }

    @Override
    public boolean updateGenericUserConfig(
            final IGenericUser  iGenericUser,
            final String        config
            )
            throws NamingException, SQLException, UnsupportedEncodingException
    {
        return updateGenericUserConfig( iGenericUser, config, defaultCharsetName );
    }

    private class MySQLIGenericUserDAO extends DAOBuilder<IGenericUser>
    {
        @Override
        public IGenericUser newInstance( ResultSet resultSet )
                throws SQLException
        {
            return new GenericUser().set( resultSet );
        }

        @Override
        public Connection getConnection() throws NamingException, SQLException
        {
            return MySQLUserDAO.this.getConnection();
        }

        @Override
        public void closeConnection( Connection connection )
                throws IllegalArgumentException, SQLException
        {
            MySQLUserDAO.this.closeConnection( connection );
        }

        /**
         * Returns 'best' object not only IGenericUser
         */
        @Override
        protected IGenericUser getElement( String sql )
            throws NamingException, SQLException
        {
            IGenericUser iGUser = super.getElement( sql );

            if( iGUser != null ) {
                if( iGUser.isInRole(  Role.BIP_USER ) ) {
                    return findUser( iGUser.getIdUser() );
                    }

                // TODO: add extra type when possible.
                }
            return iGUser;
        }
    }

    private class GenericUser implements IGenericUser
    {
        private static final long serialVersionUID = 1L;
        private int iduser;
        private String username;
        private String rolename;
        private int isactive;

        @Override
        public int getIdUser()
        {
            return iduser;
        }

        @Override
        public String getUserName()
        {
            return username;
        }

        @Override
        public int getIsActive()
        {
            return isactive;
        }

        public IGenericUser set( ResultSet resultSet ) throws SQLException
        {
            this.iduser   = resultSet.getInt( "iduser" );
            this.username = resultSet.getString( "username" );
            this.rolename = resultSet.getString( "role_name" );
            this.isactive = resultSet.getInt( "isactive" );

            return this;
        }

        @Override
        public boolean isInRole( Role role )
        {
            return RolesIdentifier.createRoleForName( rolename ).equals( role );
        }
    }

}
